using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;

namespace BlobNet.TestsCommon.DataProviderHelpers.Base
{
    //the reason for this class is just that we are expecting not only built-in DbDataProviders 
    public abstract class DbTestHelper : DataProviderTestHelper
    {
        private const string EntityStatusTable = "BlobStatusTable";
        private const string ListEntitiesTable = "BlobPartsListTable";
        
        public abstract IDbConnection NewConnection();
        protected abstract void AddParameter(IDbCommand command, string paramName, DbType fieldType, int size, object value);


        public override Dictionary<string, object> GetEntitiesStatusRow(Guid domainId, Guid entityId,
                                                                        string propertyName)
        {
            var result = new Dictionary<string, object>();
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
                        string.Format(
                            "SELECT [Id],[PropertyName],[ValueType],[FullyQualifiedType],[Value] FROM {0} WHERE Id = '{1}' AND PropertyName= N'{2}'",
                            GetBlobDomainObjectName(domainId, EntityStatusTable), entityId, propertyName);
                    conn.Open();
                    using (var sqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        if (sqlDataReader.Read())
                        {
                            result.Add("Id", sqlDataReader[0]);
                            result.Add("PropertyName", sqlDataReader[1]);
                            result.Add("ValueType", sqlDataReader[2]);
                            result.Add("FullyQualifiedType", sqlDataReader[3]);
                            result.Add("Value", ReadBytes(sqlDataReader, 4, false));
                        }
                    }
                }
                conn.Close();
            }
            return result;
        }

        public override Dictionary<string, object> GetListEntitiesRow(Guid domainId, Guid collectionId, int position)
        {
            var result = new Dictionary<string, object>();
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
                        string.Format(
                            "SELECT [FullyQualifiedType],[Value] FROM {0} WHERE Id = '{1}' AND Position= {2}",
                            GetBlobDomainObjectName(domainId, ListEntitiesTable), collectionId, position);
                    conn.Open();
                    using (var sqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        if (sqlDataReader.Read())
                        {
                            result.Add("FullyQualifiedType", sqlDataReader[0]);
                            result.Add("Value", ReadBytes(sqlDataReader, 1));
                        }
                    }
                }
                conn.Close();
            }
            return result;
        }

        public override void AddEntitiesStatusRow(Guid domainId, Guid ownerId, string propertyName, string valueType,string fullyQualifiedName,
                                                  object value)
        {
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    string commandText;
                    commandText = (value != null)
                                      ? string.Format(
                                          "INSERT INTO {0} ([Id],[PropertyName],[ValueType],[FullyQualifiedType],[Value]) VALUES('{1}',N'{2}',N'{3}',N'{4}',@value)",
                                          GetBlobDomainObjectName(domainId, EntityStatusTable), ownerId, propertyName,
                                          valueType, fullyQualifiedName)
                                      : string.Format(
                                          "INSERT INTO {0} ([Id],[PropertyName],[ValueType],[FullyQualifiedType]) VALUES('{1}',N'{2}',N'{3}',N'{4}')",
                                          GetBlobDomainObjectName(domainId, EntityStatusTable), ownerId, propertyName,
                                          valueType,fullyQualifiedName);

                    cmd.CommandText = commandText;
                    if (value != null)
                    {
                        AddParameter(cmd, "@value", DbType.Binary, 8000,GetObjectBytes(value));
                    }
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public override void AddListEntitiesRow(Guid domainId, Guid collectionId,
                                                string fullyQualifiedType, int position, object value)
        {
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    string commandText =
                        string.Format(
                            value != null
                                ? "INSERT INTO {0} ([Id],[Position],[FullyQualifiedType],[Value]) VALUES('{1}',{2},N'{3}',@value)"
                                : "INSERT INTO {0} ([Id],[Position],[FullyQualifiedType]) VALUES('{1}',{2},N'{3}')",
                            GetBlobDomainObjectName(domainId, ListEntitiesTable), collectionId, position,
                            fullyQualifiedType);
                    cmd.CommandText = commandText;

                    if (value != null)
                    {
                        AddParameter(cmd,"@value", DbType.Binary, 8000, GetObjectBytes(value));                        
                    }
                    conn.Open();
                    int n = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }


        public override void RemoveListEntitiesRow(Guid domainId, Guid collectionId, int position)
        {
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    string commandText =
                        string.Format("DELETE FROM {0} WHERE [Id]='{1}' AND [Position]= {2}",
                                      GetBlobDomainObjectName(domainId, ListEntitiesTable), collectionId, position);
                    cmd.CommandText = commandText;

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public override int CountEntitiesStatusRows(Guid domainId, Guid ownerId)
        {
            string commandText = string.Format("SELECT count(1) FROM {0} WHERE Id = '{1}'",
                                               GetBlobDomainObjectName(domainId, EntityStatusTable), ownerId);

            return ExecuteScalar(domainId, commandText);
        }

        public override int CountListEntitiesRows(Guid domainId, Guid collectionId)
        {
            string commandText = string.Format("SELECT count(1) FROM {0} WHERE Id = '{1}'",
                                               GetBlobDomainObjectName(domainId, ListEntitiesTable),
                                               collectionId);
            return ExecuteScalar(domainId, commandText);
        }

      

        protected override int ExecuteScalar(Guid domainId, string commandText)
        {
            int result;

            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    conn.Open();
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
            return result;
        }

        public override void ClearTable(Guid blobDomainId, string tableName)
        {
            using (var conn = NewConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = string.Format("delete from {0}", GetBlobDomainObjectName(blobDomainId, tableName));
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }

        }
    }
}